package com.iussoft.tdengine.util;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;

import java.lang.reflect.Method;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @description:
 * @author: Hou Dayu
 * @date: Created in 2020/11/6
 */
public class TDengineUtil {

    private static Pattern linePattern = Pattern.compile("_(\\w)");
    private static Pattern humpPattern = Pattern.compile("[A-Z]");
    public static String TDDB_CACHE_NAME="";
    public static String TDDB_CACHE_PREFIX_NAME="rpc_datasource_";

    public static void setTddbCacheName(String tddbCacheName) {
        TDDB_CACHE_NAME = tddbCacheName;
    }

    /**
     * 生成插入sql语句
     */
    public static String createInsertSql(String tableName, List<Map<String, Object>> maps) {
        if (StringUtil.isEmpty(maps)) {
            return "";
        }
        StringBuilder buffer = new StringBuilder();
        buffer.append("INSERT INTO ").append(tableName).append(" (");
        int count = maps.size();
        List<String> ks = new ArrayList();
        ks.addAll(maps.get(0).keySet());
        for (int i = 0; i < count; i++) {
            Map<String, Object> map = maps.get(i);
            int num = ks.size();
            StringBuilder keys = new StringBuilder(" ");
            StringBuilder value = new StringBuilder(" ");
            for (int q = 0; q < num; q++) {
                String key = ks.get(q);
                if (i == 0) {
                    keys.append(humpToLine(key)).append(",");
                }
                if (map.containsKey(key)) {
                    try {
                        Object obj = map.get(key);
                        if (obj.getClass().equals(Date.class)) {
                            Date d = (Date) obj;
                            value.append(d.getTime()).append(",");
                        } else {
                            value.append("'").append(obj).append("'").append(",");
                        }
                    } catch (Exception ignored) {
                    }
                } else {
                    value.append("'").append("'").append(",");
                }
            }
            value.deleteCharAt(value.length() - 1);
            if (i == 0) {
                keys.deleteCharAt(keys.length() - 1);
                buffer.append(keys).append(") VALUES ");
            }
            buffer.append("( ").append(value).append(")");
        }
        return buffer.toString();
    }

    /**
     * 生成Stable表合建语句
     */
    public static String createStableSql(String tableName,Map<String, Object> cloumns, Map<String, Object> tags){
        //CREATE TABLE shebei_tab (ts timestamp, degree double, val_bool bool,val_int int) TAGS(identifier binary(200), namespaceIndex int);
        StringBuilder sb = new StringBuilder("CREATE TABLE "+tableName+" (");
        for(Map.Entry<String,Object> entry : cloumns.entrySet()){
            sb.append(entry.getKey()+" "+entry.getValue()+",");
        }
        sb.deleteCharAt(sb.length() - 1);
        if(tags != null) {
            sb.append(" ) TAGS ( ");
            for (Map.Entry<String, Object> entry : tags.entrySet()) {
                sb.append(entry.getKey() + " " + entry.getValue() + ",");
            }
            sb.deleteCharAt(sb.length() - 1);
        }
        sb.append(" ) ");
        return sb.toString();
    }

    /**
     * 生成Stable表合建语句
     */
    public static String createStableTagSql(String tableName,String sTbaleName, String... tags){
        //CREATE TABLE field1 USING shebei_tab TAGS ('数据类型示例.8 位设备.S 寄存器', 2);
        StringBuilder sb = new StringBuilder("CREATE TABLE "+tableName+" USING "+sTbaleName );
        sb.append(" TAGS ( ");
        for (String tag : tags) {
            sb.append("'").append(tag).append("',");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" ) ");
        return sb.toString();
    }
    /**
     * 生成插入Stable的sql语句
     *
     * @param tableName
     * @param map
     * @return
     */
    public static String createInsertStableSql(String tableName, String sTbaleName, Map<String, Object> map, String... tags) {
        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO ").append(tableName);
        builder.append(stableToSQL(sTbaleName, tags));
        builder.append(mapToSQL(map));

        return builder.toString();
    }

    private static String stableToSQL(String sTableName, String... tags) {
        StringBuilder builder = new StringBuilder();
        builder.append(" using ").append(sTableName).append(" TAGS ( ");
        for (String tag : tags) {
            builder.append("'").append(tag).append("',");
        }
        builder.deleteCharAt(builder.length() - 1).append(" ) ");

        return builder.toString();
    }

    /**
     * 生成插入sql语句
     *
     * @param tableName
     * @param map
     * @return
     */
    public static String createInsertSql(String tableName, Map<String, Object> map) {
        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO ").append(tableName);
        builder.append(mapToSQL(map));

        return builder.toString();
    }

    public static String mapToSQL(Map<String, Object> map) {
        StringBuilder builder = new StringBuilder();
        Set<Map.Entry<String, Object>> set = map.entrySet();
        StringBuilder keys = new StringBuilder(" ");
        StringBuilder value = new StringBuilder(" ");
        for (Map.Entry<String, Object> entry : set) {
            keys.append(humpToLine(entry.getKey())).append(",");
            try {
                if (entry.getValue().getClass().equals(Date.class)) {
                    Date d = (Date) entry.getValue();
                    value.append(d.getTime()).append(",");
                } else {
                    value.append("'").append(entry.getValue()).append("'").append(",");
                }
            } catch (Exception ignored) {
            }
        }
        keys.deleteCharAt(keys.length() - 1);
        value.deleteCharAt(value.length() - 1);
        builder.append(" (").append(keys).append(") VALUES( ").append(value).append(")");
        return builder.toString();
    }

    /**
     * 通过setter method,获取到其对应的属性名
     */
    public static String getFieldNameBySetter(Method method) {
        return toLowerCaseFirstOne(method.getName().substring(3));
    }

    /**
     * 获取指定类型方法的所有的setter方法
     * 方法属性名为key，对应的方法为value
     */
    public static Map<String, Method> getSetterMethodsMap(Class clazz) {
        Method[] methods = clazz.getMethods();
        Map<String, Method> setterMethods = new HashMap<>(methods.length / 2);

        for (Method m : methods) {
            if (m.getName().startsWith("set")) {
                setterMethods.put(toLowerCaseFirstOne(m.getName().substring(3)), m);
            }
        }
        return setterMethods;
    }

    /**
     * 获取指定类型方法的所有的setter方法
     */
    public static Method[] getSetterMethods(Class clazz) {
        Method[] methods = clazz.getMethods();
        Method[] setterMethods = new Method[methods.length / 2];

        int i = 0;
        for (Method m : methods) {
            if (m.getName().startsWith("set")) {
                setterMethods[i] = m;
                i++;
            }
        }
        return setterMethods;
    }

    /**
     * 首字母转小写
     */
    public static String toLowerCaseFirstOne(String s) {
        if (Character.isLowerCase(s.charAt(0))) {
            return s;
        } else {
            return Character.toLowerCase(s.charAt(0)) + s.substring(1);
        }
    }

    /**
     * 首字母转大写
     */
    public static String toUpperCaseFirstOne(String s) {
        if (Character.isUpperCase(s.charAt(0))) {
            return s;
        } else {
            return Character.toUpperCase(s.charAt(0)) + s.substring(1);
        }
    }

    /**
     * 下划线转驼峰
     */
    public static String lineToHump(String str) {
        str = str.toLowerCase();
        Matcher matcher = linePattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    /**
     * 驼峰转下划线
     */
    public static String humpToLine(String str) {
        Matcher matcher = humpPattern.matcher(str);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    public static void main(String[] args) {
        DruidDataSource dsss = new DruidDataSource();
        dsss.setDriverClassName("com.taosdata.jdbc.TSDBDriver");
        dsss.setUrl("jdbc:TAOS://192.168.205.128:6030/mydb");
        dsss.setUsername("root");
        dsss.setPassword("123456");
        dsss.setInitialSize(3);
        dsss.setMaxActive(10);
        dsss.setMinIdle(3);
        dsss.setMaxWait(60000);//最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间
        dsss.setRemoveAbandonedTimeout(180);//超过时间限制，回收没有用(废弃)的连接
        dsss.setRemoveAbandoned(true);//超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收
        dsss.setPoolPreparedStatements(true);//是否预编译SQL语句

        String sql = "SELECT COUNT(*), AVG(degree), MAX(degree), MIN(degree) FROM shebei_tab GROUP BY identifier";
        sql ="show tables like 'field2'";
        Connection connection = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            connection = dsss.getConnection();
            stmt = connection.createStatement();
            Map<String,String> eners = new HashMap();
            for(int i=2;i<5;i++){
                String tableName = "energy_tab_"+i;
                String sqlTable = "CREATE TABLE "+tableName+" USING energy_table TAGS ('groupId_"+i+"','deviceId_"+i+"','"+i+"')";
                //stmt.execute(sqlTable);
                long ts = System.currentTimeMillis();
                int count =new Random().nextInt(10);
                for(int k=0;k<5;k++){
                    ts = ts+i;
                    StringBuilder energySql = new StringBuilder();
                    String str = eners.get(tableName);
                    if(StringUtils.isEmpty(str)) {
                        energySql.append(" "+tableName+" VALUES ");
                    }
                    energySql.append("('" + (new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS")).format(new Date(ts)));
                    energySql.append("',"+count).append(",'identifier-"+i+k+"',2");
                    energySql.append(") ");
                    eners.put(tableName,StringUtil.doNull(str,"") + energySql.toString());
                    count = count+(k*10);
                }

            }
            StringBuilder energySql = new StringBuilder("INSERT INTO ");
            for(Map.Entry<String,String> obj : eners.entrySet()){
                energySql.append(obj.getValue());
            }
            stmt.execute(energySql.toString());

            sql = "select SPREAD(d_value) value from energy_table where TS>=NOW-1d INTERVAL(10m) group by device_id,energy_type";
            rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            while (rs.next()) {
                DataMap<String> fn = new DataMap();
                for (int i = 1; i <= count; i++) {
                    String key = rsmd.getColumnLabel(i);
                    fn.put(key, rs.getString(i));
                }
                System.out.println(fn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != rs)
                    rs.close();
                if (null != stmt)
                    stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (null != connection)
                        connection.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}
